﻿Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class clsBD
    Dim ConexionSAGE As OdbcConnection = Nothing
    Dim ConexionSQL As SqlConnection = Nothing

    '--------------------------------------------------------------------------
    ' Rutinas de acceso a SQL Server
    '--------------------------------------------------------------------------
    Public Sub ConectarSQL(Optional ByVal CadenaConexion As String = "")
        If CadenaConexion = "" Then
            'CadenaConexion = My.Settings.CCTermoNavision
        End If
        If ConexionSQL Is Nothing Then
            ConexionSQL = New SqlConnection(CadenaConexion)
        End If
        ConexionSQL.Open()
    End Sub
    Public Sub CerrarSQL()
        If ConexionSQL.State = ConnectionState.Open Then
            ConexionSQL.Close()
        End If
    End Sub

    Public Function cargaTablaSQL(ByVal SQL As String, Optional ByVal Parametros As ArrayList = Nothing) As DataTable
        Dim Tabla As New DataTable
        Dim Comando As New SqlCommand(SQL, ConexionSQL)
        If Not Parametros Is Nothing Then
            Dim Param As Parametro
            For Each Param In Parametros
                Comando.Parameters.AddWithValue(Param.Nombre, Param.Valor)
            Next
        End If
        Dim DA As New SqlDataAdapter(Comando)
        DA.Fill(Tabla)
        Return Tabla
    End Function

    Public Sub EjecutarSQL(ByVal SQL As String)
        Dim Comando As New SqlCommand(SQL, ConexionSQL)
        Comando.ExecuteNonQuery()
    End Sub


    '--------------------------------------------------------------------------
    ' Rutinas de acceso a SAGE
    '--------------------------------------------------------------------------
    Public Function CrearDSNSage(ByVal DSN As String) As String

        Dim CadenaConexion = "DSN=" & DSN & _
            ";UID=Procesos" & _
            ";PWD=2365"
        Return CadenaConexion

    End Function

    Public Sub ConectarSAGE(ByVal DSN As String)
        If ConexionSAGE Is Nothing Then
            ConexionSAGE = New OdbcConnection(CrearDSNSage(DSN))
        End If
        ConexionSAGE.Open()
    End Sub
    Public Sub CerrarSAGE()
        If ConexionSAGE.State = ConnectionState.Open Then
            ConexionSAGE.Close()
        End If
    End Sub

    Public Function cargaTablaSAGE(ByVal SQL As String) As DataTable
        Dim Tabla As New DataTable
        Dim Comando As New OdbcCommand(SQL, ConexionSAGE)
        Dim DA As New OdbcDataAdapter(Comando)
        DA.Fill(Tabla)
        Return Tabla
    End Function

    Public Sub EjecutarSAGE(ByVal SQL As String)
        Dim Comando As New OdbcCommand(SQL, ConexionSAGE)
        Comando.ExecuteNonQuery()
    End Sub

    Public Class Parametro
        Sub New(ByVal _Nombre As String, ByVal _Valor As Object)
            Nombre = _Nombre
            Valor = _Valor
        End Sub
        Public Nombre As String
        Public Valor As Object
    End Class

End Class
